# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
工具类，实现了对参数进行组合，生产不同组合的dict类型的参数，并将dict参数保存到list中，方便取用。 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
import xlrd
from qbmsloan_day_api import API_Day
from qbmsloan_month_api import API_Month
import copy
from datetime import datetime
from xlrd import xldate_as_tuple

class parameter():

    # 进件参数传入；case_base_info，audit_record，contact
    def get_jinjian_data(self,xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            product = table.cell_value(i, 0)  # 第1列的产品类别
            api = table.cell_value(i, 1)     # 第2列的接口名称
            caseId = table.cell_value(i, 2)

            if product == '60天产品' and api == 'jinjian' and caseId != '':
                data_list = API_Day['jinjian']
                data_list['caseId'] = caseId
                data_list['age'] = int(table.cell_value(i, 3))
                data_list['maxQuota'] = table.cell_value(i, 4)
                data_list['applyRate'] = table.cell_value(i, 5)
                data_list['applyTerm'] = int(table.cell_value(i, 6))
                data_list['loanType'] = table.cell_value(i, 7)
                status = str(int(table.cell_value(i, 8)))
                data_list['name'] = table.cell_value(i, 9)
                data_list['idCard'] = table.cell_value(i, 10)
                day_copy = copy.copy(data_list)         # 潜拷贝,不在受原来对象的影响
                row.append(day_copy)
                row.append(status)
                cellData.append(row)
            elif product == '分期产品' and api == 'jinjian' and caseId != '':
                month_list = API_Month['jinjian']
                month_list['caseId'] = caseId
                month_list['age'] = int(table.cell_value(i, 3))
                month_list['maxQuota'] = table.cell_value(i, 4)
                month_list['applyRate'] = table.cell_value(i, 5)
                month_list['applyTerm'] = int(table.cell_value(i, 6))
                month_list['loanType'] = table.cell_value(i, 7)
                status = str(int(table.cell_value(i, 8)))
                month_list['name'] = table.cell_value(i, 9)
                month_list['idCard'] = table.cell_value(i, 10)
                month_copy = copy.copy(month_list)  # 潜拷贝,不在受原来对象的影响
                row.append(month_copy)
                row.append(status)
                cellData.append(row)
            else:
                continue  # 不符合要求就跳过
        return(cellData)


    # 代付穿透参数传入,通过钱包支付接口放款到商户，商户内部付给个人，接口不考虑个人银行账号:transaction_in_log
    def get_payout_data(self,xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            product = table.cell_value(i, 0)  # 第1列的产品类别
            api = table.cell_value(i, 1)     # 第2列的接口名称
            loanId = table.cell_value(i, 12)
            if product == '60天产品' and api == 'jinjian' and loanId != '':
                data_list = API_Month['payout']
                data_list['loanId'] = loanId
                data_list['userName'] = table.cell_value(i, 13)
                data_list['idCard'] = table.cell_value(i, 14)
                data_list['amount'] = table.cell_value(i, 15)
                data_list['batchNo'] = table.cell_value(i, 16)
                data_list['orderId'] = table.cell_value(i, 17)
                day_copy = copy.copy(data_list)         # 潜拷贝,不在受原来对象的影响
                row.append(day_copy)
                try:
                    status = str(int(table.cell_value(i, 18)))
                    row.append(status)
                except:
                    status = table.cell_value(i, 18)
                    row.append(status)
                cellData.append(row)
            elif product == '分期产品' and api == 'jinjian' and loanId != '':
                month_list = API_Month['payout']
                month_list['loanId'] = loanId
                month_list['userName'] = table.cell_value(i, 13)
                month_list['idCard'] = table.cell_value(i, 14)
                month_list['amount'] = table.cell_value(i, 15)
                month_list['batchNo'] = table.cell_value(i, 16)
                month_list['orderId'] = table.cell_value(i, 17)
                day_copy = copy.copy(month_list)  # 潜拷贝,不在受原来对象的影响
                row.append(day_copy)
                try:
                    status = str(int(table.cell_value(i, 18)))
                    row.append(status)
                except:
                    status = table.cell_value(i, 18)
                    row.append(status)
                cellData.append(row)
            else:
                continue
        return(cellData)


    # 放款成功后推送合同信息:loancontract_verify,loan_contract,contract_record
    def get_contract_data(self,xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            product = table.cell_value(i, 0)  # 第1列的产品类别
            api = table.cell_value(i, 1)     # 第2列的接口名称
            loan_id = table.cell_value(i, 20)
            if product == '60天产品' and api == 'jinjian' and loan_id != '':
                data_list = API_Day['contract']
                data_list['loan_id'] = loan_id
                data_list['name'] = table.cell_value(i, 21)
                data_list['id_card'] = table.cell_value(i, 22)
                data_list['loan_amount'] = table.cell_value(i, 23)
                data_list['single_rate'] = table.cell_value(i, 24)
                data_list['loan_term'] = int(table.cell_value(i, 25))
                data_list['ct_no'] = table.cell_value(i, 17)   # 支付订单
                day_copy = copy.copy(data_list)
                row.append(day_copy)
                try:
                    status = int(table.cell_value(i, 26))
                    row.append(status)
                except:
                    status = table.cell_value(i, 26)
                    row.append(status)
                cellData.append(row)
            elif product == '分期产品' and api == 'jinjian' and loan_id != '':
                month_list = API_Month['contract']
                month_list['loan_id'] = loan_id
                month_list['name'] = table.cell_value(i, 21)
                month_list['id_card'] = table.cell_value(i, 22)
                month_list['loan_amount'] = table.cell_value(i, 23)
                month_list['single_rate'] = table.cell_value(i, 24)
                month_list['loan_term'] = int(table.cell_value(i, 25))
                month_list['ct_no'] = table.cell_value(i, 17)   # 支付订单
                day_copy = copy.copy(month_list)
                row.append(day_copy)
                try:
                    status = int(table.cell_value(i, 26))
                    row.append(status)
                except:
                    status = table.cell_value(i, 26)
                    row.append(status)
                cellData.append(row)
            else:
                continue
        return(cellData)


    # 合同签署成功后推送放款记录和还款计划:loanrecord_verify,loan_record,repayment_plan,jrb_loancontract,jrb_loaninfo
    def get_paymentPlan_data(self,xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            product = table.cell_value(i, 0)  # 第1列的产品类别
            api = table.cell_value(i, 1)     # 第2列的接口名称
            loan_id = table.cell_value(i, 28)
            if product == '60天产品' and api == 'jinjian' and loan_id != '':
                data_list = API_Day['repaymentPlan']
                data_list['loanRecord']['loan_id'] = loan_id
                data_list['loanRecord']['contract_id'] = table.cell_value(i, 29)
                data_list['loanRecord']['name'] = table.cell_value(i, 30)
                data_list['id_card'] = table.cell_value(i, 31)
                data_list['loanRecord']['loan_amount'] = int(table.cell_value(i, 32))
                data_list['loanRecord']['loan_rate'] = table.cell_value(i, 33)
                data_list['loanRecord']['loan_term'] = int(table.cell_value(i, 34))
                data_list['loanRecord']['received_time'] = datetime(*xldate_as_tuple(table.cell_value(i, 35),0)).strftime('%Y-%m-%d %H:%M:%S')
                data_list['repaymentPlans'][0]['contract_id'] = table.cell_value(i, 29)
                data_list['repaymentPlans'][0]['repayment_time'] = datetime(*xldate_as_tuple(table.cell_value(i, 36),0)).strftime('%Y-%m-%d %H:%M:%S')
                data_list['repaymentPlans'][0]['repayment_amount'] = int(table.cell_value(i, 37))
                data_list['repaymentPlans'][0]['repayment_interest'] = int(table.cell_value(i, 32))*0.024
                data_list['repaymentPlans'][0]['repayment_principal'] = int(table.cell_value(i, 32))
                data_list['loanRecord']['pay_flow_num'] = table.cell_value(i, 16)
                data_list['loanRecord']['ct_no'] = table.cell_value(i, 17)
                day_copy = copy.deepcopy(data_list)    # 深拷贝
                row.append(day_copy)
                try:
                    status = str(int(table.cell_value(i, 38)))
                    row.append(status)
                except:
                    status = table.cell_value(i, 38)
                    row.append(status)
                cellData.append(row)
            elif product == '分期产品' and api == 'jinjian' and loan_id != '':
                month_list = API_Month['repaymentPlan']
                month_list['loanRecord']['loan_id'] = loan_id
                month_list['loanRecord']['contract_id'] = table.cell_value(i, 29)
                month_list['loanRecord']['name'] = table.cell_value(i, 30)
                month_list['id_card'] = table.cell_value(i, 31)
                month_list['loanRecord']['loan_amount'] = int(table.cell_value(i, 32))
                month_list['loanRecord']['loan_rate'] = table.cell_value(i, 33)
                month_list['loanRecord']['loan_term'] = int(table.cell_value(i, 34))
                month_list['loanRecord']['loan_time'] = datetime(*xldate_as_tuple(table.cell_value(i, 35),0)).strftime('%Y-%m-%d %H:%M:%S')
                month_list['repaymentPlans'][0]['contract_id'] = table.cell_value(i, 29)
                month_list['repaymentPlans'][0]['repayment_time'] = datetime(*xldate_as_tuple(table.cell_value(i, 36),0)).strftime('%Y-%m-%d %H:%M:%S')
                month_list['repaymentPlans'][0]['repayment_amount'] = int(table.cell_value(i, 37))
                month_list['repaymentPlans'][0]['repayment_interest'] = int(table.cell_value(i, 32))*0.024
                month_list['repaymentPlans'][0]['repayment_principal'] = int(table.cell_value(i, 32))
                month_list['loanRecord']['pay_flow_num'] = table.cell_value(i, 16)
                month_list['loanRecord']['ct_no'] = table.cell_value(i, 17)
                day_copy = copy.deepcopy(month_list)    # 深拷贝
                row.append(day_copy)
                try:
                    status = str(int(table.cell_value(i, 38)))
                    row.append(status)
                except:
                    status = table.cell_value(i, 38)
                    row.append(status)
                cellData.append(row)
            else:
                continue
        return(cellData)


    def get_payment_data(self,xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            product = table.cell_value(i, 0)  # 第1列的产品类别
            api = table.cell_value(i, 1)     # 第2列的接口名称
            loan_id = table.cell_value(i, 28)
            if product == '分期产品' and api == 'jinjian' and loan_id != '':
                month_list = API_Month['repaymentPlan']
                month_list['loanRecord']['loan_id'] = loan_id
                month_list['loanRecord']['contract_id'] = table.cell_value(i, 29)
                month_list['loanRecord']['name'] = table.cell_value(i, 30)
                month_list['id_card'] = table.cell_value(i, 31)
                month_list['loanRecord']['loan_amount'] = int(table.cell_value(i, 32))
                month_list['loanRecord']['loan_rate'] = table.cell_value(i, 33)
                month_list['loanRecord']['loan_term'] = int(table.cell_value(i, 34))
                month_list['loanRecord']['loan_time'] = datetime(*xldate_as_tuple(table.cell_value(i, 35),0)).strftime('%Y-%m-%d %H:%M:%S')
                month_list['repaymentPlans'][0]['contract_id'] = table.cell_value(i, 29)
                month_list['repaymentPlans'][0]['repayment_time'] = datetime(*xldate_as_tuple(table.cell_value(i, 36),0)).strftime('%Y-%m-%d %H:%M:%S')
                month_list['repaymentPlans'][0]['repayment_amount'] = int(table.cell_value(i, 37))
                month_list['repaymentPlans'][0]['repayment_interest'] = int(table.cell_value(i, 32))*0.024
                month_list['repaymentPlans'][0]['repayment_principal'] = int(table.cell_value(i, 32))
                month_list['loanRecord']['pay_flow_num'] = table.cell_value(i, 16)
                month_list['loanRecord']['ct_no'] = table.cell_value(i, 17)
                day_copy = copy.deepcopy(month_list)    # 深拷贝
                row.append(day_copy)
                try:
                    status = str(int(table.cell_value(i, 38)))
                    row.append(status)
                except:
                    status = table.cell_value(i, 38)
                    row.append(status)
                cellData.append(row)
            else:
                continue
        return(cellData)
